﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.FreightAddress;
using Dapper;
using MongoDB.Driver;

namespace BCData.TMS.FreightAddress
{
    public class FreightAddressData : IFreightAddressData
    {
        #region 私有变量

        protected readonly IDatabaseContext databaseContext;

        private readonly string QUERY = @" SELECT A.FreightAddressId, A.ProvinceCode, A.CityCode, A.CountyCode, A.UserId, A.Address, A.Longitude, A.Latitude, A.UsedCount,
	                                    A.FreightContactId, A.CreateTime, B.UserName, C.ProvinceName, D.CityName, E.CountyName, B.Phone  FROM
	                                    FreightAddress A
	                                    LEFT JOIN USER B ON A.UserId=B.UserId
	                                    LEFT JOIN Province C ON A.ProvinceCode=C.ProvinceCode
	                                    LEFT JOIN City D ON A.CityCode=D.CityCode
	                                    LEFT JOIN County E  ON A.CountyCode=E.CountyCode ";

        #endregion 私有变量

        #region 构造函数

        public FreightAddressData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        #endregion 构造函数

        #region 公共方法

        public async Task<bool> CheckAddressAsync(long userId, string countyCode, string cityCode, string provinceCode, string address)
        {
            var sql = "SELECT 1 FROM freightaddress WHERE UserId=?UserId AND CountyCode=?CountyCode AND CityCode=?CityCode AND ProvinceCode=?ProvinceCode AND Address=?Address";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId);
            parameters.Add("?CountyCode", countyCode);
            parameters.Add("?CityCode", cityCode);
            parameters.Add("?ProvinceCode", provinceCode);
            parameters.Add("?Address", address);

            var res = await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters);
            return res.ToInt32() > 0;
        }

        public async Task<FreightAddressExtendEntity> GetAsync(long userId, string countyCode, string cityCode, string provinceCode, string address)
        {
            var sql = $"{QUERY}  WHERE A.UserId=?UserId AND A.CountyCode=?CountyCode AND A.CityCode=?CityCode AND A.ProvinceCode=?ProvinceCode AND A.Address=?Address LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId);
            parameters.Add("?CountyCode", countyCode);
            parameters.Add("?CityCode", cityCode);
            parameters.Add("?ProvinceCode", provinceCode);
            parameters.Add("?Address", address);

            return await this.databaseContext.Master.QueryEntityAsync<FreightAddressExtendEntity>(sql, parameters);
        }

        public async Task<bool> CheckAddressAsync(long freightAddressId)
        {
            var sql = "SELECT 1 FROM FreightAddress WHERE FreightAddressId=?FreightAddressId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightAddressId", freightAddressId);
            var res = await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters);
            return res.ToInt32() > 0;
        }

        public async Task<FreightAddressExtendEntity> InsertAsync(FreightAddressEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("FreightAddress", new[] { "ProvinceCode", "CityCode", "CountyCode", "UserId", "Address", "Longitude", "Latitude" });
            sql = string.Format("{0} {1} WHERE FreightAddressId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", entity.CountyCode);
            parameters.Add("?CityCode", entity.CityCode);
            parameters.Add("?ProvinceCode", entity.ProvinceCode);
            parameters.Add("?UserId", entity.UserId);
            parameters.Add("?Address", entity.Address);
            parameters.Add("?Longitude", entity.Longitude);
            parameters.Add("?Latitude", entity.Latitude);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync(sql, parameters);
        }

        public async Task<int> UpdateAsync(FreightAddressEntity entity)
        {
            var sql = SqlCommands.GetUpdateSql("FreightAddress", new[] { "ProvinceCode", "CityCode", "CountyCode", "UserId", "Address", "Longitude", "Latitude" }, new[] { "FreightAddressId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", entity.CountyCode);
            parameters.Add("?CityCode", entity.CityCode);
            parameters.Add("?ProvinceCode", entity.ProvinceCode);
            parameters.Add("?UserId", entity.UserId);
            parameters.Add("?Address", entity.Address);
            parameters.Add("?Longitude", entity.Longitude);
            parameters.Add("?Latitude", entity.Latitude);
            parameters.Add("?FreightAddressId", entity.FreightAddressId);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<int> UpdateUserCountAsync(long freightAddressId)
        {
            var sql = @"UPDATE FreightAddress A,( SELECT UsedCount FROM freightaddress WHERE FreightAddressId = ?FreightAddressId ) B SET A.UsedCount = B.UsedCount + 1 WHERE A.FreightAddressId =?FreightAddressId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightAddressId", freightAddressId);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<FreightAddressExtendEntity> GetByIdAsync(long freightAddressId)
        {
            var sql = $"{QUERY} WHERE FreightAddressId=?FreightAddressId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FreightAddressId", freightAddressId);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<FreightAddressExtendEntity>(sql, parameters);
        }

        public async Task<List<FreightAddressExtendEntity>> GetByUserIdAsync(long userId)
        {
            var sql = $"{QUERY} WHERE A.UserId=?UserId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId);
            var data = await this.databaseContext.Slave.QueryAsync<FreightAddressExtendEntity>(sql, parameters);
            return data.ToList();
        }

        public async Task<(IEnumerable<FreightAddressExtendEntity>, int)> GetAllAsync(int pageIndex, int pageSize, string userName, string phone)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(userName))
            {
                wheres.Add(" B.UserName=?UserName");
                parameters.Add("?UserName", userName);
            }

            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add(" B.Phone=?Phone");
                parameters.Add("?Phone", phone);
            }
            parameters.Add("?pageIndex", pageIndex);
            parameters.Add("?pageSize", pageSize);

            var where = wheres.Count > 0 ? "WHERE " + string.Join(" AND", wheres) : "";

            var sql = string.Format("{0} {1} LIMIT ?pageIndex,?pageSize ; {2}",
                QUERY, where,
                $"{string.Format(QUERY, "COUNT(1)")} {@where}");
            return await this.databaseContext.Slave.QueryMultipleAsync<FreightAddressExtendEntity, int>(sql, parameters);
        }

        public async Task<FreightAddressExtendEntity> GetAsync(long userId, double longitude, double latitude)
        {
            string sql = string.Format("{0} WHERE A.UserId=?UserId AND A.Longitude=?Longitude AND A.Latitude=?Latitude", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId);
            parameters.Add("?Longitude", longitude);
            parameters.Add("?Latitude", latitude);
            return await this.databaseContext.Slave.QueryEntityAsync<FreightAddressExtendEntity>(sql, parameters);
        }



        #endregion 公共方法
    }
}